﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

using AutoWelding.Models;

using MySql.Data.MySqlClient;

namespace AutoWelding.DAL
{
    public class UserDAL
    {
        // 加载用户信息
        public List<UserModel> GetUsers(int userStatus, string username, int isDeletedCode)
        {
            string selectSql = "select * from user_info where 1= 1";
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            // 状态过滤
            if (userStatus != 2)
            {
                selectSql += " and user_status = @userStatus";
                parameters.Add(new MySqlParameter("@userStatus", MySqlDbType.Int32) { Value = userStatus });
            }
            // 用户名模糊匹配
            if (!string.IsNullOrEmpty(username) || username != "")
            {
                selectSql += " and nick_name like @username";
                parameters.Add(new MySqlParameter("@username", MySqlDbType.VarChar) { Value = "%" + username + "%" });
            }
            // 软删除状态
            selectSql += " and is_delete = @isDeletedCode";
            parameters.Add(new MySqlParameter("@isDeletedCode", MySqlDbType.Int32) { Value = isDeletedCode });
            // 排序
            //selectSql += " ORDER BY create_time DESC";

            Console.Write(selectSql);
            return MysqlHelp.ExecuteQuery<UserModel>(selectSql,
                 reader => new UserModel
                 {
                     UserId = Convert.ToInt32(reader["user_id"]),
                     NickName = reader["nick_name"].ToString(),
                     UserName = reader["user_name"].ToString(),
                     UserPassword = reader["user_password"].ToString(),
                     UserStatus = Convert.ToInt32(reader["user_status"]),
                     IsDelete = Convert.ToInt32(reader["is_delete"]),
                     CreateTime = Convert.ToDateTime(reader["create_time"]),
                     UserIdentity = Convert.ToInt32(reader["user_identity"]),
                     Remark = reader["remark"].ToString()
                 },
                 parameters.ToArray()
                 );
        }

        /// <summary>
        /// 根据id获取用户信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public UserModel GetUserById(int userId)
        {
            string sql = "SELECT * FROM user_info WHERE user_id = @userId";
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            parameters.Add(new MySqlParameter("@userId", MySqlDbType.Int32) { Value = userId });

            // 使用 ExecuteQuery 方法获取 DataTable
            DataTable dataTable = MysqlHelp.ExecuteQuery(sql, parameters.ToArray());

            // 检查是否返回了数据
            if (dataTable.Rows.Count > 0)
            {
                // 从 DataTable 中提取数据并映射到 UserModel 对象
                DataRow row = dataTable.Rows[0];
                UserModel user = new UserModel
                {
                    UserId = Convert.ToInt32(row["user_id"]),
                    UserName = row["user_name"].ToString(),
                    NickName = row["nick_name"].ToString(),
                    UserPassword = row["user_password"].ToString(),
                    UserIdentity = Convert.ToInt32(row["user_identity"]),
                    CreateTime = Convert.ToDateTime(row["create_time"]),
                    UpdateTime = row["update_time"] != DBNull.Value ? Convert.ToDateTime(row["update_time"]) : (DateTime?)null,
                    UserStatus = Convert.ToInt32(row["user_status"]),
                    IsDelete = Convert.ToInt32(row["is_delete"]),
                    Remark = row["remark"].ToString()
                    // 映射其他字段...
                };
                return user;
            }
            else
            {
                return null; // 如果没有找到数据，返回 null
            }
        }


        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="user"></param>
        /// <exception cref="NotImplementedException"></exception>
        public int UpdateUser(UserModel user)
        {
            string updateSql = "UPDATE user_info SET " +
                "nick_name = @nickName," +
                "user_name = @userName," +
                "user_password = @userPassword," +
                "user_status = @userStatus," +
                "user_identity = @userIdentity," +
                "remark = @remark," +
                "update_time = @updateTime," +
                "is_delete = @isDelete " +
                "WHERE " +
                "user_id = @userId";

            MySqlParameter[] parameters =
            {
                new MySqlParameter("@userId", MySqlDbType.Int32) { Value = user.UserId },
                new MySqlParameter("@nickName", MySqlDbType.VarChar) { Value = user.NickName },
                new MySqlParameter("@userName", MySqlDbType.VarChar) { Value = user.UserName },
                new MySqlParameter("@userPassword", MySqlDbType.VarChar) { Value = user.UserPassword },
                new MySqlParameter("@userStatus", MySqlDbType.Int32) { Value = user.UserStatus },
                new MySqlParameter("@userIdentity", MySqlDbType.Int32) { Value = user.UserIdentity },
                new MySqlParameter("@remark", MySqlDbType.VarChar) { Value = user.Remark },
                new MySqlParameter("@updateTime", MySqlDbType.DateTime) { Value = DateTime.Now },
                new MySqlParameter("@isDelete", MySqlDbType.Int32) { Value = user.IsDelete }
            };
            try
            {

                int result = MysqlHelp.ExecuteNonQuery(updateSql,parameters);
                return result;
            }
            catch (Exception e)
            {
                Console.Write("更改用户信息发生错误：" + e.Message);
                Console.Write(updateSql);
                return 0;
            }

        }

        /// <summary>
        /// 新增用户信息
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int InsertUser(UserModel user)
        {
            string InsertSql = @"
                    INSERT INTO user_info (
                    nick_name, 
                    user_name, 
                    user_password, 
                    user_status, 
                    user_identity, 
                    remark, 
                    create_time
                    ) VALUES (
                    @nickName, 
                    @userName, 
                    @userPassword, 
                    @userStatus, 
                    @userIdentity, 
                    @remark, 
                    @createTime
                    )";
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@nickName", MySqlDbType.VarChar) { Value = user.NickName },
                new MySqlParameter("@userName", MySqlDbType.VarChar) { Value = user.UserName },
                new MySqlParameter("@userPassword", MySqlDbType.VarChar) { Value = user.UserPassword },
                new MySqlParameter("@userStatus", MySqlDbType.Int32) { Value = user.UserStatus },
                new MySqlParameter("@userIdentity", MySqlDbType.Int32) { Value = user.UserIdentity },
                new MySqlParameter("@remark", MySqlDbType.VarChar) { Value = user.Remark ?? (object)DBNull.Value },
                new MySqlParameter("@createTime", MySqlDbType.DateTime) { Value = user.CreateTime == default ? DateTime.Now : user.CreateTime }
            };
            int result = MysqlHelp.ExecuteNonQuery(InsertSql, parameters);
            return result;
        }
        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int DeleteUser(int userId)
        {
            MySqlParameter mySqlParameter = new MySqlParameter("@userId", userId);
            string deleteSql = "delete from user_info where user_id = @userId";
            return MysqlHelp.ExecuteNonQuery(deleteSql, mySqlParameter); ;
        }

        /// <summary>
        /// 逻辑删除用户信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int LogicDeleteUser(List<int> userIds)
        {
            // 动态生成 SQL 和参数
            string sql = @"
                        UPDATE user_info 
                        SET is_delete = 0 
                        WHERE user_id IN (" + string.Join(",", userIds.Select(id => $"@id{id}")) + ")";

            List<MySqlParameter> parameters = new List<MySqlParameter>();
            foreach (int userId in userIds)
            {
                parameters.Add(new MySqlParameter($"@id{userId}", MySqlDbType.Int32) { Value = userId });
            }
            // 执行更新操作
            return MysqlHelp.ExecuteNonQuery(sql, parameters.ToArray());
        }

        /// <summary>
        /// 检查用户名是否已存在
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public int CheckUserIsExist(string username)
        {
            string sql = "SELECT COUNT(*) FROM user_Info WHERE user_name = @username";
            MySqlParameter parameter = new MySqlParameter("@username", MySqlDbType.VarChar) { Value = username };
            object result = MysqlHelp.ExecuteScalar(sql, parameter);
            return Convert.ToInt32(result);
        }

        /// <summary>
        /// 判断用户名是否已被其他用户使用（排除自己）
        /// </summary>
        public bool IsUsernameExists(string username, int excludeUserId)
        {
            string sql = "SELECT COUNT(*) FROM user_info WHERE user_name = @username AND user_id <> @excludeUserId";
            var parameters = new[]
            {
        new MySqlParameter("@username", MySqlDbType.VarChar) { Value = username },
        new MySqlParameter("@excludeUserId", MySqlDbType.Int32) { Value = excludeUserId }
    };

            object result = MysqlHelp.ExecuteScalar(sql, parameters);
            int count = Convert.ToInt32(result);
            return count > 0;
        }
    }
}
